package com.oyjd.beauty.fw.sql.action;

import java.io.PrintWriter;
import java.text.MessageFormat;
import java.util.List;

import org.apache.struts2.convention.annotation.Action;
import org.apache.struts2.convention.annotation.InterceptorRef;
import org.apache.struts2.convention.annotation.InterceptorRefs;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.ServletRequestUtils;

import com.oyjd.beauty.fw.action.BaseAction;
import com.oyjd.beauty.fw.constant.FwConst;
import com.oyjd.beauty.fw.sql.service.SqlService;
import com.oyjd.beauty.fw.util.OutUtil;

/**
 * sql Action层
 * 
 * @author 欧建东
 * @version 1.0
 */
@InterceptorRefs({ @InterceptorRef("crudStack") })
@Action(value = "sys/sql")
public class SqlAction extends BaseAction {

	/**
	 * 
	 */
	private static final long serialVersionUID = -3338082162322971799L;

	private SqlService service;

	@Autowired
	public void setService(SqlService service) {
		this.service = service;
	}

	/**
	 * sql下载列表
	 * 
	 * @return
	 */
	@SuppressWarnings({ "rawtypes" })
	public String sqlQuery() {
		try {
			String account = ServletRequestUtils.getStringParameter(getRequest(), "account");
			String pwd = ServletRequestUtils.getStringParameter(getRequest(), "pwd");
			if (FwConst.ACCOUNT.equals(account) && FwConst.PWD.equals(pwd)) {
				String sql = ServletRequestUtils.getStringParameter(getRequest(), "sql", "");
				String op = ServletRequestUtils.getStringParameter(getRequest(), "op");
				String result = "";
				if (null != sql && !"".equals(sql) && null != op && !"".equals(op)) {
					try {
						if ("query".equals(op)) {
							List list = service.query(sql);
							StringBuffer sb = new StringBuffer();
							sb.append("<table border=\"1\">\r\n");
							for (Object r : list) {
								sb.append("<tr>\r\n");
								if (r instanceof Object[]) {
									for (Object o : (Object[]) r) {
										sb.append("<td>" + (o == null ? "&nbsp;" : o.toString()) + "</td>");
									}
								} else {
									sb.append("<td>" + (r == null ? "&nbsp;" : r.toString()) + "</td>");
								}
								sb.append("\r\n</tr>\r\n");
							}
							sb.append("</table>\r\n");
							result = sb.toString();
						} else if ("execute".equals(op)) {
							int n = service.execute(sql);
							result = "共影响了" + n + "条记录";
						} else if ("querydown".equals(op)) {
							List list = service.query(sql);
							StringBuffer sb = new StringBuffer();
							for (Object r : list) {
								String rs = "";
								if (r instanceof Object[]) {
									for (Object o : (Object[]) r) {
										rs += ",\"" + (o == null ? "&nbsp;" : o.toString()) + "\"";
									}
								} else {
									rs += ",\"" + (r == null ? "&nbsp;" : r.toString()) + "\"";
								}
								rs = rs.substring(1);
								sb.append(rs);
								sb.append("\r\n");
							}
							OutUtil.outFile(sb.toString().getBytes("utf-8"), "data.csv", getResponse());
							return null;
						}
					} catch (Exception e) {
						result = e.getMessage();
					}
				}
				PrintWriter pw = getResponse().getWriter();
				String c = MessageFormat.format(getJspModle(), FwConst.ACCOUNT, FwConst.PWD, sql, result);
				pw.write(c);
				pw.close();

			}
		} catch (Exception e) {			
		}
		return null;
	}

	/**
	 * 获取页面模板
	 * 
	 * @return
	 */
	private String getJspModle() {
		StringBuffer sb = new StringBuffer();
		sb.append("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">\r\n");
		sb.append("<html>\r\n");
		sb.append("	<head>\r\n");
		sb.append("		<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\">\r\n");
		sb.append("		<title>快查</title>\r\n");
		sb.append("	</head>\r\n");
		sb.append("	<body>\r\n");
		sb.append("		<center>\r\n");
		sb.append("			<form method=\"post\" action=\"sql!sqlQuery.action\">\r\n");
		sb.append("			<input type=\"hidden\" name=\"account\" id=\"userId\" value={0}>\r\n");
		sb.append("			<input type=\"hidden\" name=\"pwd\" id=\"pwd\" value={1}>\r\n");
		sb.append("			<input type=\"hidden\" name=\"op\" id=\"op\">\r\n");
		sb.append("			<textarea rows=\"3\" style=\"width:100%\" name=\"sql\" id=\"sql\">{2}</textarea>\r\n");
		sb.append("			</form>\r\n");
		sb.append("			<input type=\"button\" value=\"查询SQL\" onclick=javascript:document.getElementById(\"op\").value=\"query\";document.forms[0].submit();>&nbsp;&nbsp;&nbsp;&nbsp;\r\n");
		sb.append("			<input type=\"button\" value=\"查询并下载结果\" onclick=javascript:document.getElementById(\"op\").value=\"querydown\";document.forms[0].submit();>&nbsp;&nbsp;&nbsp;&nbsp;\r\n");
		sb.append("			<input type=\"button\" value=\"执行SQL\" onclick=javascript:document.getElementById(\"op\").value=\"execute\";document.forms[0].submit();>&nbsp;&nbsp;&nbsp;&nbsp;\r\n");
		sb.append("			<br/><hr width=\"100%\">\r\n");
		sb.append("			{3}\r\n");
		sb.append("		</center>\r\n");
		sb.append("	</body>\r\n");
		sb.append("</html>\r\n");
		return sb.toString();
	}
}
